﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DelonixRegiaHotel.Classes;

namespace DelonixRegiaHotel.DBManager
{
    class AdvBookingDBManager
    {
        //retrieve
        public static ArrayList GetAllCheckInDetails()
        {
            ArrayList result = new ArrayList();

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select * from CheckInOut";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    CheckInOut temp = new CheckInOut();
                    temp.CheckID = Convert.ToString(dr["checkID"]);
                    temp.CheckInTime = Convert.ToDateTime(dr["checkInTime"]);
                    temp.CheckOutTime = Convert.ToDateTime(dr["checkOutTime"]);
                    temp.CustomerID = Convert.ToString(dr["customerID"]);
                    temp.RoomNo = Convert.ToString(dr["roomNo"]);
                    temp.PaymentType = Convert.ToString(dr["paymentType"]);
                    result.Add(temp);
                }

                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static ArrayList GetRoomsByDateAndSmoke(DateTime checkIn, DateTime checkOut)
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select roomPrice,roomDetails,roomTypeName,r.roomNo ,smkRoom from Room r inner join RoomType rt on rt.roomTypeID = r.roomTypeID where roomNo not in (select roomNo from CheckInOut where checkInTime >= @checkIn and checkInTime <= @checkOut or checkOutTime >= @checkIn and checkOutTime <= @checkOut)";
                comm.Parameters.AddWithValue("@checkIn", checkIn);
                comm.Parameters.AddWithValue("@checkOut", checkOut);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Room temp = new Room();
                    RoomType temp2 = new RoomType();
                    temp.RoomNo = Convert.ToString(dr["roomNo"]);
                    temp.SmkRoom = Convert.ToString(dr["smkRoom"]);
                    temp2.RoomPrice = Convert.ToDecimal(dr["roomPrice"]);
                    temp2.RoomDetails = Convert.ToString(dr["roomDetails"]);
                    temp2.RoomTypeName = Convert.ToString(dr["roomTypeName"]);
                    result.Add(temp);
                    result.Add(temp2);
                }

                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static ArrayList GetRoomInfoByIDRmNo(string roomNo)
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select roomPrice,roomDetails,roomTypeName,r.roomNo from Room r, RoomType rt where rt.roomTypeID = r.roomTypeID and r.roomNo = @roomNo";
                comm.Parameters.AddWithValue("@roomNo", roomNo);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    Room temp = new Room();
                    RoomType temp2 = new RoomType();
                    temp.RoomNo = Convert.ToString(dr["roomNo"]);
                    temp2.RoomPrice = Convert.ToDecimal(dr["roomPrice"]);
                    temp2.RoomDetails = Convert.ToString(dr["roomDetails"]);
                    temp2.RoomTypeName = Convert.ToString(dr["roomTypeName"]);
                    result.Add(temp);
                    result.Add(temp2);
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        
        public static bool testWhetherCheckIDExists(string checkID)
        {
            bool sucess = false;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select * from CheckInOut where checkID=@checkID";
                comm.Parameters.AddWithValue("@checkID", checkID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    sucess = true;
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return sucess;
        }

        public static ArrayList GetBookingInfoByCheckID(string checkID)//still doing
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select * from CheckInOut where checkID=@checkID";
                comm.Parameters.AddWithValue("@checkID", checkID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    //get check in info
                    CheckInOut getCheckInInfo = new CheckInOut();
                    getCheckInInfo.CustomerID = Convert.ToString(dr["customerID"]);
                    getCheckInInfo.RoomNo = Convert.ToString(dr["roomNo"]);
                    getCheckInInfo.CheckInTime = Convert.ToDateTime(dr["checkInTime"]);
                    getCheckInInfo.NumOfAdult = Convert.ToInt32(dr["numOfAdult"]);
                    getCheckInInfo.NumOfChild = Convert.ToInt32(dr["numOfChild"]);
                    getCheckInInfo.NumOfDays = Convert.ToInt32(dr["numOfDays"]);
                    getCheckInInfo.CheckOutTime = Convert.ToDateTime(dr["checkOutTime"]);
                    getCheckInInfo.PaymentType = Convert.ToString(dr["paymentType"]);
                    getCheckInInfo.RoomRate = Convert.ToDecimal(dr["roomRate"]);
                    getCheckInInfo.Balance = Convert.ToDecimal(dr["balance"]);
                    getCheckInInfo.Deposit = Convert.ToDecimal(dr["deposit"]);
                    getCheckInInfo.Remarks = Convert.ToString(dr["remarks"]);
                    result.Add(getCheckInInfo);
                    dr.Close();
                    
                    // get customer info
                    SqlConnection conn1 = null;
                    conn1 = new SqlConnection();
                    conn1.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                    conn1.Open();
                    SqlCommand comm1 = new SqlCommand();
                    comm1.Connection = conn;
                    comm1.CommandText = "select * from Customer where customerID = @customerID";
                    comm1.Parameters.AddWithValue("@customerID", getCheckInInfo.CustomerID);
                    SqlDataReader dr1 = comm1.ExecuteReader();
                    if(dr1.Read())
                    {
                        Customer getCustomerInfo = new Customer();
                        getCustomerInfo.FirstName = Convert.ToString(dr1["firstName"]);
                        getCustomerInfo.LastName = Convert.ToString(dr1["lastName"]);
                        getCustomerInfo.Nric = Convert.ToString(dr1["nric"]);
                        getCustomerInfo.HomeAddress = Convert.ToString(dr1["homeAddress"]);
                        getCustomerInfo.PostalCode = Convert.ToInt32(dr1["postalCode"]);
                        getCustomerInfo.Country = Convert.ToString(dr1["country"]);
                        getCustomerInfo.Dob = Convert.ToDateTime(dr1["dob"]);
                        getCustomerInfo.Email = Convert.ToString(dr1["email"]);
                        getCustomerInfo.ContactNo = Convert.ToInt32(dr1["contactNo"]);
                        result.Add(getCustomerInfo);
                    }
                    dr1.Close();
                    conn1.Close();

                    //get room info
                    SqlConnection conn2 = null;
                    conn2 = new SqlConnection();
                    conn2.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                    conn2.Open();
                    SqlCommand comm2 = new SqlCommand();
                    comm2.Connection = conn;
                    comm2.CommandText = "select smkRoom,roomPrice,roomDetails,roomTypeName,r.roomNo from Room r, RoomType rt where rt.roomTypeID = r.roomTypeID and r.roomNo = @roomNo";
                    comm2.Parameters.AddWithValue("@roomNo", getCheckInInfo.RoomNo);
                    SqlDataReader dr2 = comm2.ExecuteReader();
                    if(dr2.Read())
                    {
                        Room getRoomInfo = new Room();
                        RoomType getRoomTypeInfo = new RoomType();
                        getRoomInfo.RoomNo = Convert.ToString(dr2["roomNo"]);
                        getRoomInfo.SmkRoom = Convert.ToString(dr2["smkRoom"]);
                        getRoomTypeInfo.RoomPrice = Convert.ToDecimal(dr2["roomPrice"]);
                        getRoomTypeInfo.RoomDetails = Convert.ToString(dr2["roomDetails"]);
                        getRoomTypeInfo.RoomTypeName = Convert.ToString(dr2["roomTypeName"]);
                        result.Add(getRoomInfo);
                        result.Add(getRoomTypeInfo);
                    }
                    dr2.Close();
                    conn2.Close();
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        //delete
        public static int DeleteBookingByID(string checkID)
        {
            DeleteModRecordIfAny(checkID);
            int rowsDeleted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "delete from CheckInOut where checkID=@checkID";
                comm.Parameters.AddWithValue("@checkID",checkID);
                rowsDeleted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return rowsDeleted;
        }

        public static void DeleteModRecordIfAny(string checkID)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from BookingDetailsModRecord where checkID=@checkID";
                comm.Parameters.AddWithValue("@checkID",checkID);
                SqlDataReader dr = comm.ExecuteReader();
                if(dr.Read())
                {
                    SqlConnection conn1 = null;
                    conn1 = new SqlConnection();
                    conn1.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                    conn1.Open();
                    SqlCommand comm1 = new SqlCommand();
                    comm1.Connection = conn1;
                    comm1.CommandText = "delete from BookingDetailsModRecord where checkID = @checkID";
                    comm1.Parameters.AddWithValue("@checkID", checkID);
                    comm1.ExecuteNonQuery();
                    conn1.Close();
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //create
        public static int InsertNewBookingInfo(CheckInOut newBookingInfo)
        {
            int rowsinserted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Insert into CheckInOut values (@checkID, @deposit, @balance, @paymentType, @checkInTime, @checkOutTime, @numOfAdult, @numOfChild, @numOfKey, @extraCostTotal, @roomRate, @remarks, @roomNo, @customerID, @numOfDays, @roomStatus)";
                comm.Parameters.AddWithValue("@checkID", newBookingInfo.CheckID);
                comm.Parameters.AddWithValue("@deposit", newBookingInfo.Deposit);
                comm.Parameters.AddWithValue("@balance", newBookingInfo.Balance);
                comm.Parameters.AddWithValue("@paymentType", newBookingInfo.PaymentType);
                comm.Parameters.AddWithValue("@checkInTime", newBookingInfo.CheckInTime);
                comm.Parameters.AddWithValue("@checkOutTime", newBookingInfo.CheckOutTime);
                comm.Parameters.AddWithValue("@numOfAdult", newBookingInfo.NumOfAdult);
                comm.Parameters.AddWithValue("@numOfChild", newBookingInfo.NumOfChild);
                comm.Parameters.AddWithValue("@numOfKey", newBookingInfo.NumOfKey);
                comm.Parameters.AddWithValue("@extraCostTotal", newBookingInfo.ExtraCostTotal);
                comm.Parameters.AddWithValue("@roomRate", newBookingInfo.RoomRate);
                comm.Parameters.AddWithValue("@remarks", newBookingInfo.Remarks);
                comm.Parameters.AddWithValue("@roomNo", newBookingInfo.RoomNo);
                comm.Parameters.AddWithValue("@customerID", newBookingInfo.CustomerID);
                comm.Parameters.AddWithValue("@numOfDays", newBookingInfo.NumOfDays);
                comm.Parameters.AddWithValue("@roomStatus", newBookingInfo.RoomStatus);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return rowsinserted;
        }


        //update checkinout with insert new booking mod record
        public static int UpdateCheckInOutByCheckId(CheckInOut checkInInfo)
        {
            int rowsupdated = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE CheckInOut SET deposit=@deposit,balance=@balance,paymentType=@paymentType,checkInTime=@checkInTime,checkOutTime=@checkOutTime,numOfAdult=@numOfAdult,numOfChild=@numOfChild,roomRate=@roomRate,remarks=@remarks,roomNo=@roomNo,numOfDays=@numOfDays where checkID=@checkID";
                comm.Parameters.AddWithValue("@checkID", checkInInfo.CheckID);
                comm.Parameters.AddWithValue("@deposit", checkInInfo.Deposit);
                comm.Parameters.AddWithValue("@balance", checkInInfo.Balance);
                comm.Parameters.AddWithValue("@paymentType", checkInInfo.PaymentType);
                comm.Parameters.AddWithValue("@checkInTime", checkInInfo.CheckInTime);
                comm.Parameters.AddWithValue("@checkOutTime", checkInInfo.CheckOutTime);
                comm.Parameters.AddWithValue("@numOfAdult", checkInInfo.NumOfAdult);
                comm.Parameters.AddWithValue("@numOfChild", checkInInfo.NumOfChild);
                comm.Parameters.AddWithValue("@roomRate", checkInInfo.RoomRate);
                comm.Parameters.AddWithValue("@remarks", checkInInfo.Remarks);
                comm.Parameters.AddWithValue("@roomNo", checkInInfo.RoomNo);
                comm.Parameters.AddWithValue("@numOfDays", checkInInfo.NumOfDays);
                rowsupdated = comm.ExecuteNonQuery();
                if(rowsupdated == 1)
                {
                    //check whether is it the same staff editing the same checkID, conn1 is either closed in if or else
                    SqlConnection conn1 = null;
                    conn1 = new SqlConnection();
                    conn1.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                    conn1.Open();
                    SqlCommand comm1 = new SqlCommand();
                    comm1.Connection = conn1;
                    comm1.CommandText = "select * from BookingDetailsModRecord where staffID=@staffID and checkID=@checkID";
                    comm1.Parameters.AddWithValue("@staffID", StaticSession.StaffID);
                    comm1.Parameters.AddWithValue("@checkID", checkInInfo.CheckID);
                    SqlDataReader dr1 = comm1.ExecuteReader();
                    if(dr1.Read()) 
                    {
                        //if read, it means that this staff has edited this particular checkid before to prevent duplicate primary key, just do update of timeofmod
                        SqlConnection conn2 = null;
                        conn2 = new SqlConnection();
                        conn2.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                        conn2.Open();
                        SqlCommand comm2 = new SqlCommand();
                        comm2.Connection = conn2;
                        comm2.CommandText = "UPDATE BookingDetailsModRecord SET timeOfMod=@timeOfMod where staffID=@staffID and checkID=@checkID";
                        comm2.Parameters.AddWithValue("@timeOfMod", DateTime.Now);
                        comm2.Parameters.AddWithValue("@staffID", StaticSession.StaffID);
                        comm2.Parameters.AddWithValue("@checkID", checkInInfo.CheckID);
                        comm2.ExecuteNonQuery();
                        conn1.Close();
                        conn2.Close();
                    }
                    else
                    {
                        conn1.Close();
                        SqlConnection conn3 = null;
                        conn3 = new SqlConnection();
                        conn3.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                        conn3.Open();
                        SqlCommand comm3 = new SqlCommand();
                        comm3.Connection = conn3;
                        comm3.CommandText = "Insert into BookingDetailsModRecord values (@staffID,@checkID,@timeOfMod)";
                        comm3.Parameters.AddWithValue("@staffID", StaticSession.StaffID);
                        comm3.Parameters.AddWithValue("@checkID", checkInInfo.CheckID);
                        comm3.Parameters.AddWithValue("@timeOfMod", DateTime.Now);
                        comm3.ExecuteNonQuery();
                        conn3.Close();
                    }
                }
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }


    }
}
